package agentea;

import java.rmi.RMISecurityManager;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import agentzia.Erreserba;
import agentzia.Irteerak;

public class AgenteDatuBasea {
	private static AgenteDatuBasea instantzia;
    private Connection konexioa;

    /**
    * Datu basea hasieratzen
    */
    public AgenteDatuBasea()
    {
        try
            {
//            String url = "jdbc:odbc:bidaiak";
//            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        	Class.forName("com.mysql.jdbc.Driver");
            konexioa = DriverManager.getConnection("jdbc:mysql://localhost/agentea", "agentzia", "");
        }
        catch (SQLException anException)
            {
            while (anException != null)
                {
                System.out.println("SQL Exception:  " + anException.getMessage());
                anException = anException.getNextException();
            }
        }
        catch (java.lang.Exception anException)
            {
            anException.printStackTrace();
        }
    }
    
    public static AgenteDatuBasea instance()
    {
        if (instantzia == null)
            instantzia = new AgenteDatuBasea();
        return instantzia;
    }
    
    public Irteerak[] getIrteerak(){
    	PreparedStatement selectSententzia;
    	PreparedStatement selectSententzia2;
        PreparedStatement selectSententzia3;
    	ResultSet resultSet;
    	ResultSet resultSet2;
        ResultSet resultSet3;
    	
    	Irteerak[] irteerak;
    	int irteeraKop = 0;
    	
    	try
        {
	        // Select sententzia sortu
	        selectSententzia = konexioa.prepareStatement("SELECT COUNT(irteeraKod) FROM Irteerak");
	        resultSet = selectSententzia.executeQuery();
	        if(resultSet.next()){
	        	irteeraKop = resultSet.getInt(1);
	        }
    		selectSententzia =
	            konexioa.prepareStatement("SELECT irteeraKod,lekua,pertsonaKop,prezioaEgun FROM Irteerak");
	        resultSet = selectSententzia.executeQuery();
	        
	        irteerak = new Irteerak[irteeraKop];
	        Irteerak irteera;
	        int i = 0;
	        while(resultSet.next()){
                // Sortu objektua
	        	irteera = new Irteerak(resultSet.getInt(1), resultSet.getString(2), resultSet.getInt(3), resultSet.getFloat(4));
                // Lortu datak eta gorde objektuan
	        	selectSententzia2 = konexioa.prepareStatement("SELECT COUNT(irteeraData) FROM Irteerak_Datak WHERE irteeraKod = '"+resultSet.getInt(1)+"'");
	        	resultSet2 = selectSententzia2.executeQuery();
	        	int dataKop = 0;
	        	if(resultSet2.next()){
	        		dataKop = resultSet2.getInt(1);
	        	}
	        	irteera.setDatakSize(dataKop);
	        	int j = 0;
	        	selectSententzia2 = konexioa.prepareStatement("SELECT irteeraData FROM Irteerak_Datak WHERE irteeraKod = ?");
	        	selectSententzia2.setInt(1, resultSet.getInt(1));
	        	resultSet2 = selectSententzia2.executeQuery();
	        	while(resultSet2.next()){
	        		irteera.addData(resultSet2.getString(1), j);
	        		j++;
	        	}
                // Lortu egun kopuruak eta gorde objektuan
                selectSententzia3 = konexioa.prepareStatement("SELECT COUNT(EgunKopurua) FROM Irteerak_Egunak WHERE irteeraKod = '"+resultSet.getInt(1)+"'");
                resultSet3 = selectSententzia3.executeQuery();
                int egunKop = 0;
                if(resultSet3.next()){
                    egunKop = resultSet3.getInt(1);
                }
                irteera.setEgunakSize(egunKop);
                int k = 0;
                selectSententzia3 = konexioa.prepareStatement("SELECT egunKopurua FROM Irteerak_Egunak WHERE irteeraKod = ?");
                selectSententzia3.setInt(1, resultSet.getInt(1));
                resultSet3 = selectSententzia3.executeQuery();
                while (resultSet3.next()) {
                    irteera.addEgunak(resultSet3.getInt(1), k);
                    k++;
                }
                
	        	irteerak[i] = irteera;
	        	i++;
	        }
	        return irteerak;
        }catch (SQLException anException)
	        {
	        while (anException != null)
	            {
	        	System.out.println("SQL Exception:  " + anException.getMessage());
	            anException = anException.getNextException();
	            
	        }
	    }
	    catch (java.lang.Exception anException)
	        {
	        System.out.println("Exception:  " + anException.getMessage());
	        }
    	return null;
    }

    public void libratuPlazak(int irteeraKod,int plazaKop) {
    	PreparedStatement selectSententzia;
    	ResultSet resultSet;
    	
    	try {
    		selectSententzia = konexioa.prepareStatement("SELECT pertsonaKop FROM Irteerak WHERE irteeraKod = ?");
    		selectSententzia.setInt(1, irteeraKod);
    		resultSet = selectSententzia.executeQuery();
    		if(resultSet.next()){
    			int pertsonaKop = resultSet.getInt(1);
    			selectSententzia = konexioa.prepareStatement("UPDATE Irteerak SET pertsonaKop = ? WHERE irteeraKod = ?");
        		selectSententzia.setInt(1, pertsonaKop + plazaKop);
    			selectSententzia.setInt(2, irteeraKod);
    			int update = selectSententzia.executeUpdate();
    		}
    	}catch (Exception e){
    		e.printStackTrace();
    	}
    }
    
    public int okupatuPlazak(int irteeraKod,int plazaKop) {
    	PreparedStatement selectSententzia;
    	ResultSet resultSet;
    	
    	try {
    		selectSententzia = konexioa.prepareStatement("SELECT pertsonaKop FROM Irteerak WHERE irteeraKod = ?");
    		selectSententzia.setInt(1, irteeraKod);
    		resultSet = selectSententzia.executeQuery();
    		if(resultSet.next()){
    			int pertsonaKop = resultSet.getInt(1);
    			if(pertsonaKop > plazaKop){
	    			selectSententzia = konexioa.prepareStatement("UPDATE Irteerak SET pertsonaKop = ? WHERE irteeraKod = ?");
	        		selectSententzia.setInt(1, pertsonaKop - plazaKop);
	    			selectSententzia.setInt(2, irteeraKod);
	    			int update = selectSententzia.executeUpdate();
	    			return 1;
    			}else{
    				return 0;
    			}
    		}
    		return 0;
    	}catch (Exception e){
    		e.printStackTrace();
    		return 0;
    	}
    }
    	
    	
//    	selectSententzia = konexioa.prepareStatement("INSERT INTO erreserbak_irteerak)

    
}
